EMT Practice Test

1. Question Content...

---

Question1: You have a table named JSON_TBL which has a variant column JSON_VAR. The json stored in that table looks as below
{
"COURSE_DESC": "SNOWFLAKE CERTIFICATION",
"COURSE_ID": 1000,
"DURATION": 2
}
if you run a query SELECT JSON_VAR:Course_id FROM JSON_TBL; what will it return

Question2: Snowflake has row level security

Question3: Materialized views based on external tables can improve query performance

Question4: Which command will you run to list all privileges and roles granted to the role

Question5: A stream stores data with the same columns as the source data but with additional columns.
What are those additional columns?

Question6: Create a task and a stream following the below steps. So, when the
system$stream_has_data('rawstream1') condition returns false, what will happen to the task ?
-- Create a landing table to store raw JSON data.
-- Snowpipe could load data into this table. create or replace table raw (var variant);
-- Create a stream to capture inserts to the landing table.
-- A task will consume a set of columns from this stream. create or replace stream rawstream1 on table raw;
-- Create a second stream to capture inserts to the landing table.
-- A second task will consume another set of columns from this stream. create or replace stream rawstream2 on table raw;
-- Create a table that stores the names of office visitors identified in the raw data. create or replace table names (id int, first_name string, last_name string);
-- Create a table that stores the visitation dates of office visitors identified in the raw data.
create or replace table visits (id int, dt date);
-- Create a task that inserts new name records from the rawstream1 stream into the names table
-- every minute when the stream contains records.
-- Replace the 'etl_wh' warehouse with a warehouse that your role has USAGE privilege on. create or replace task raw_to_names
warehouse = etl_wh schedule = '1 minute' when
system$stream_has_data('rawstream1') as
merge into names n
using (select var:id id, var:fname fname, var:lname lname from rawstream1) r1 on n.id = to_number(r1.id)
when matched then update set n.first_name = r1.fname, n.last_name = r1.lname
when not matched then insert (id, first_name, last_name) values (r1.id, r1.fname, r1.lname)
;
-- Create another task that merges visitation records from the rawstream1 stream into the visits table
-- every minute when the stream contains records.
-- Records with new IDs are inserted into the visits table;
-- Records with IDs that exist in the visits table update the DT column in the table.
-- Replace the 'etl_wh' warehouse with a warehouse that your role has USAGE privilege on. create or replace task raw_to_visits
warehouse = etl_wh schedule = '1 minute' when
system$stream_has_data('rawstream2') as
merge into visits v
using (select var:id id, var:visit_dt visit_dt from rawstream2) r2 on v.id = to_number(r2.id) when matched then update set v.dt = r2.visit_dt
when not matched then insert (id, dt) values (r2.id, r2.visit_dt)
;
-- Resume both tasks.
alter task raw_to_names resume;
alter task raw_to_visits resume;
-- Insert a set of records into the landing table. insert into raw
select parse_json(column1) from values
('{"id": "123","fname": "Jane","lname": "Smith","visit_dt": "2019-09-17"}'),
('{"id": "456","fname": "Peter","lname": "Williams","visit_dt": "2019-09-17"}');
-- Query the change data capture record in the table streams select * from rawstream1;
select * from rawstream2;

Question7: While creating a clustering key, what is the recommendation for maximum number of columns that you can include as part of the key?

Question8: What is a valid object hierarchy when building a Snowflake environment?

Question9: Which security, governance, and data protection features require, at a MINIMUM, the Business Critical edition of Snowflake? (Choose two.)

Question10: Schema owner can grant object privileges in a regular schema

Question11: A Snowflake Architect is designing a multi-tenant application strategy for an organization in the Snowflake Data Cloud and is considering using an Account Per Tenant strategy.
Which requirements will be addressed with this approach? (Choose two.)

Question12: You have a very large table which is already clustered on columns that are used to retrieve data from the table by a business group. The base table data does not change much. Another business group came to you and requested for a relatively small subset of data from the table which they will query using complex aggregation logic. You know that querying with those columns will take a lot of time because the table is not clustered on those columns.
What is the most optimal solution that you will suggest to the business team?

Question13: select metadata$filename, metadata$file_row_number from @filestage/data1.json.gz;
Please select the correct statements for the above-mentioned query.

Question14: When loading data into a table that captures the load time in a column with a default value of either CURRENT_TIME() or CURRENT_TIMESTAMP() what will occur?

Question15: Please select the correct hierarchy from below

Question16: When would you usually consider to add clustering key to a table

Question17: Select the true statements about TASKS

Question18: Out of the three query optimization techniques(search optimization, clustering and materialized view) which one does not have storage cost

Question19: Data sharing is supported only between provider and consumer accounts in same region

Question20: Files arrive in an external stage every 10 seconds from a proprietary system. The files range in size from 500 K to 3 MB. The data must be accessible by dashboards as soon as it arrives.
How can a Snowflake Architect meet this requirement with the LEAST amount of coding? (Choose two.)

Question21: How is the change of local time due to daylight savings time handled in Snowflake tasks? (Choose two.)

Question22: Which command below will load data from result_scan to a table?

Question23: You have created a table as below
CREATE TABLE EMPLOYEE(EMPLOYEE_ID NUMBER, EMPLOYEE_NAME VARCHAR);
What datatype Snowflake will use for EMPLOYEE_ID?

Question24: A company has a Snowflake account named ACCOUNTA in AWS us-east-1 region. The company stores its marketing data in a Snowflake database named MARKET_DB. One of the company's business partners has an account named PARTNERB in Azure East US 2 region. For marketing purposes the company has agreed to share the database MARKET_DB with the partner account.
Which of the following steps MUST be performed for the account PARTNERB to consume data from the MARKET_DB database?

Question25: An Architect would like to save quarter-end financial results for the previous six years.
Which Snowflake feature can the Architect use to accomplish this?

Question26: You ran a query and the query
SELECT * FROM inventory WHERE BIBNUMBER = 2805127;
The query profile looks as below. If you would like to further tune the query, what is the best thing to do?

Question27: You have written a procedure and a function in snowflake.
How do you find your procedures and functions in snowflake?

Question28: For authentication, snowflake Kafka connector relies on:

Question29: If a multi-cluster warehouse is resized, the new size applies to

Question30: If your role does not own the share, but owns the objects in the share, how can you block access to the objects

Question31: Arrange in order of performance(least to high)
1. External Table
2. External Table with partitioning
3. Materialized View Vs External table
4. Directly querying a file from the stage

Question32: It is recommended to assign ACCOUNTADMIN role to atleast two user

Question33: You have a medium warehouse with auto suspend of 5 minutes. You ran a query on table#1. After 10 minutes, you ran a query that joins table#1 and table#2, but you see that the query did not use data
cache.
Why?

Question34: For which use cases, will you use cross-cloud and cross-region replication?

Question35: Which command below will only copy the table structure from the existing table to the new table?

Question36: You have created a TASK in snowflake.
How will you resume it?

Question37: An Architect has been asked to clone schema STAGING as it looked one week ago, Tuesday June 1st at 8:00 AM, to recover some objects.
The STAGING schema has 50 days of retention.
The Architect runs the following statement:
CREATE SCHEMA STAGING_CLONE CLONE STAGING at (timestamp => '2021-06-01 08:00:00'); The Architect receives the following error: Time travel data is not available for schema STAGING. The requested time is either beyond the allowed time travel period or before the object creation time.
The Architect then checks the schema history and sees the following:
CREATED_ON|NAME|DROPPED_ON
2021-06-02 23:00:00 | STAGING | NULL
2021-05-01 10:00:00 | STAGING | 2021-06-02 23:00:00
How can cloning the STAGING schema be achieved?

Question38: Data replication in snowflake helps in

Question39: Which semi structured data function interprets an input string as a JSON document, producing a VARIANT value.

Question40: When loading data from stage using COPY INTO, what options can you specify for the ON_ERROR clause?

Question41: Select the true statement

Question42: In the default access control hierarchy, both securityadmin and sysadmin are owned by accountadmin

Question43: A company's client application supports multiple authentication methods, and is using Okta.
What is the best practice recommendation for the order of priority when applications authenticate to Snowflake?

Question44: You have a need to make external file data available to your users with the lowest latency. The files are on an external stage in AWS.
What feature of Snowflake is the most appropriate to use

Question45: Which command can be run to list all shares that have been created in your account or are available to consume by your account

Question46: With default settings for multi cluster warehouse, how does snowflake determines when to start a new cluster?

Question47: How do you validate the data that is unloaded using COPY INTO command

Question48: Snowflake supports the following query performance optimizing methods

Question49: For this object, Snowflake executes code outside Snowflake; the executed code is known as remote service.
What is this object called?

Question50: Shares are first-class objects in Snowflake for which Snowflake provides a set of DDL commands for creating and managing shares

Question51: Files stored in snowflake internal stage are automatically encrypted using either AES 128 or 256 strong encryption.

Question52: Below are the rest APIs provided by Snowpipe

Question53: You have created a table as below
CREATE TABLE SNOWFLAKE (FLAKE_ID INTEGER, UDEMY_COURSE VARCHAR);
Which of the below select query will fail for this table?

Question54: What will happen if you try to ALTER a COLUMN(which has NULL values) to set it to NOT NULL

Question55: There are two databases in an account, named fin_db and hr_db which contain payroll and employee data, respectively. Accountants and Analysts in the company require different permissions on the objects in these databases to perform their jobs. Accountants need read-write access to fin_db but only require read-only access to hr_db because the database is maintained by human resources personnel.
An Architect needs to create a read-only role for certain employees working in the human resources department.
Which permission sets must be granted to this role?

Question56: Which of the below commands lists all the pipes for which you have access?

Question57: How does a standard virtual warehouse policy work in Snowflake?

Question58: Who can view account-level Credit and Storage Usage?

Question59: When a database gets cloned, what accesses are replicated

Question60: Which privilege grants ability to set a Column-level Security masking policy on a table or view column.

Question61: One of your colleagues has submitted a long running query in Snowflake. how long the query can run till snowflake automatically cancels the query?

Question62: Bytes spilled to remote storage in query profile indicates volume of data spilled to remote disk

Question63: An Architect has chosen to separate their Snowflake Production and QA environments using two separate Snowflake accounts.
The QA account is intended to run and test changes on data and database objects before pushing those changes to the Production account. It is a requirement that all database objects and data in the QA account need to be an exact copy of the database objects, including privileges and data in the Production account on at least a nightly basis.
Which is the LEAST complex approach to use to populate the QA account with the Production account's data and database objects on a nightly basis?

Question64: To convert JSON null value to SQL null value, you will use

Question65: You have a table named customer_table. You want to create another table as customer_table_other which will be same as customer_table with respect to schema and data.
What is the best option?

Question66: One of your joins is taking a lot of time. The query profile view looks like this.
What may be the issue?

Question67: Every Snowflake table loaded by the Kafka connector has a schema consisting of two VARIANT columns.
Which are those?

Question68: As of today snowflake supports replication for databases only

Question69: Which of the two are limitations of the insertReport API of SnowPipe?

Question70: Which alter command below may affect the availability of column with respect to time travel?